package by.epam.communalservices.dao.jdbc;

import java.sql.*;
import java.util.*;

import org.apache.log4j.Logger;

import by.epam.communalservices.dao.DaoJkhTeam;
import by.epam.communalservices.entity.JkhTeam;
import by.epam.communalservices.exception.JkhDaoException;

public class JdbcDaoJkhTeam implements DaoJkhTeam {

    private Connection con;

    private final String COLUMN_TEAM_ID = "jkh_team_id";

    private final String COLUMN_OWNER_ID = "brig_owner_id";
    
    private final String COLUMN_SURNAME = "brig_surname";

    private final String COLUMN_NAME = "brig_name";

    private final String COLUMN_PATRONYMIC = "brig_patronymic";

    static Logger JdbcDaoTeamLoger = Logger.getLogger(JdbcDaoJkhTeam.class.getName());
    
    public JdbcDaoJkhTeam(Connection con) throws JkhDaoException {
        try {
            if (!con.isClosed())
                this.con = con;
            else {
                throw new JkhDaoException(
                        "Connection error. Connection is not open",
                        new Exception());
            }
        } catch (JkhDaoException jex) {
            throw jex;
        } catch (SQLException sx) {
            JdbcDaoTeamLoger.error("Connection error",sx);
            sx.printStackTrace();
            throw new JkhDaoException("Unknown connection error",
                    new Exception());

        }
    }

    public Long create(JkhTeam jkhTeam) {
        JdbcDaoTeamLoger.debug("create(JkhTeam jkhTeam");
        JdbcDaoTeamLoger.info("method create team");
        Long genid = null;
        String sql = "INSERT INTO public.jkh_team (brig_surname,"
                + "brig_name,brig_patronymic,brig_owner_id) VALUES (?,?,?,?)";
        ResultSet result = null;
        PreparedStatement pstate = null;
        try {
            try {
                pstate = con.prepareStatement(sql,
                        Statement.RETURN_GENERATED_KEYS);
                pstate.setString(1, jkhTeam.getSurname());
                pstate.setString(2, jkhTeam.getName());
                pstate.setString(3, jkhTeam.getPatronymic());
                pstate.setLong(4, jkhTeam.getIdOwner());
                pstate.execute();
                result = pstate.getGeneratedKeys();
                if (result.next()) {
                    genid = result.getLong(1);
                } else {
                    throw new SQLException();
                }
            } catch (SQLException ex) {
                JdbcDaoTeamLoger.error("query execute error", ex);
                ex.printStackTrace();
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    JdbcDaoTeamLoger.error("Error close result", ex);
                    ex.printStackTrace();
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    JdbcDaoTeamLoger.error("Error close statement", ex);
                    ex.printStackTrace();
                }
        }
        return genid;
    }

    public JkhTeam read(Long id) {
        JdbcDaoTeamLoger.debug("JkhTeam read(Long id)");
        JdbcDaoTeamLoger.info("method read team");
        String sql = "SELECT * FROM public.jkh_team WHERE jkh_team_id = ?";
        JkhTeam jkhTeam = null;
        ResultSet result = null;
        PreparedStatement pstate = null;
        try {
            try {
                pstate = con.prepareStatement(sql);
                pstate.setLong(1, id);
                result = pstate.executeQuery();
            } catch (SQLException ex) {
                System.out.println("query execute error");
                ex.printStackTrace();
            }
            try {
                if (result.next()) {
                    jkhTeam = new JkhTeam(
                            result.getLong(COLUMN_TEAM_ID),
                            result.getLong(COLUMN_OWNER_ID),
                            result.getString(COLUMN_SURNAME),
                            result.getString(COLUMN_NAME),
                            result.getString(COLUMN_PATRONYMIC));
                } else {
                    throw new SQLException();
                }
            } catch (SQLException ex) {
                System.out.println("Record not found");
                ex.printStackTrace();
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    System.out.print("Error close result");
                    ex.printStackTrace();
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    System.out.print("Error close statement");
                    ex.printStackTrace();
                }
        }
        return jkhTeam;
    }

    public void update(Long id, JkhTeam jkhTeam) {
        JdbcDaoTeamLoger.debug("update(Long id, JkhTeam jkhTeam)");
        JdbcDaoTeamLoger.info("method read team");
        PreparedStatement pstate = null;
        String sql = "UPDATE public.jkh_team SET brig_surname = ?, "
                + "brig_name = ?, brig_patronymic = ?, brig_owner_id = ? where jkh_team_id = ?";
        try {
            try {
                pstate = con.prepareStatement(sql);
                pstate.setString(1, jkhTeam.getSurname());
                pstate.setString(2, jkhTeam.getName());
                pstate.setString(3, jkhTeam.getPatronymic());
                pstate.setLong(4, jkhTeam.getIdOwner());
                pstate.setLong(5, id);
                pstate.execute();
            } catch (SQLException ex) {
                JdbcDaoTeamLoger.error("query execute error", ex);
                ex.printStackTrace();
            }
        } finally {
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    JdbcDaoTeamLoger.error("Error close statement", ex);
                    ex.printStackTrace();
                }
        }
    }

    public void delete(Long id) {
        JdbcDaoTeamLoger.debug("delete(Long id)");
        JdbcDaoTeamLoger.info("method delete team");
        String sql = "DELETE FROM public.jkh_team where jkh_team_id = ?";
        PreparedStatement pstate = null;
        try {
            try {
                pstate = con.prepareStatement(sql);
                pstate.setLong(1, id);
                pstate.execute();
            } catch (SQLException ex) {
                System.out.println("query execute error");
                ex.printStackTrace();
            }
        } finally {
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    System.out.print("Error close statement");
                    ex.printStackTrace();
                }
        }
    }

    public List<JkhTeam> findAll() {
        @SuppressWarnings("unchecked")
        List<JkhTeam> list = Collections.EMPTY_LIST;
        JdbcDaoTeamLoger.debug("delete(Long id)");
        JdbcDaoTeamLoger.info("method returns a list of team");
        Statement state = null;
        ResultSet result = null;
        try {
            try {
                String sql = "SELECT * FROM public.jkh_team ORDER BY jkh_team_id";
                state = con.createStatement();
                result = state.executeQuery(sql);
                list = new ArrayList<JkhTeam>();
                while (result.next()) {
                    JkhTeam jkhTeam = new JkhTeam(
                            result.getLong(COLUMN_TEAM_ID),
                            result.getLong(COLUMN_OWNER_ID),
                            result.getString(COLUMN_SURNAME),
                            result.getString(COLUMN_NAME),
                            result.getString(COLUMN_PATRONYMIC));
                    list.add(jkhTeam);
                }

            } catch (SQLException ex) {
                JdbcDaoTeamLoger.error("Error compiling the list", ex);
                ex.printStackTrace(System.out);
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    JdbcDaoTeamLoger.error("Error close result", ex);
                    ex.printStackTrace();
                }
            if (state != null)
                try {
                    state.close();
                } catch (SQLException ex) {
                    JdbcDaoTeamLoger.error("Error close statement", ex);
                    ex.printStackTrace();
                }
        }
        return list;
    }

}
